﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class RC_TransferBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void insert(int cmd_seq,int tmp_emp_seq,int move_seq,int tmp_edu_seq,string old_pos_name
                ,int old_pos_type_seq ,int old_class_seq ,string old_job_site_name, double old_salary 
                ,double salary,string pos_name ,string pos_ref_no,int class_seq,int job_site_seq
                ,string job_site_name, string start_date, int pos_type_seq,string remarks, int created_by)
        {
            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try                          
            {                
                dbManager.Open();
                dbManager.CreateParameters(21);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);
                dbManager.AddParameters(9, "@salary",salary);
                dbManager.AddParameters(10, "@pos_name", pos_name);
                dbManager.AddParameters(11, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(12, "@class_seq", class_seq);
                dbManager.AddParameters(13, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(14, "@job_site_name", job_site_name);
                dbManager.AddParameters(15, "@start_date", start_date);
                dbManager.AddParameters(16, "@remarks", remarks);
                dbManager.AddParameters(17, "@record_status", SystemInfo._ActiveRecordStatus );
                dbManager.AddParameters(18, "@created_by", created_by);
                dbManager.AddParameters(19, "@created_when", DateTime.Today);
                dbManager.AddParameters(20, "@pos_type_seq", pos_type_seq);
           
                  string sql;

                  sql = " insert into hrm_ps_command_add(cmd_seq,tmp_emp_seq,move_seq,tmp_edu_seq,old_pos_name,"
      + " old_pos_type_seq ,old_class_seq ,old_job_site_name,old_salary ,salary,"
      + " pos_name ,pos_ref_no,class_seq,job_site_seq ,job_site_name,"
      + " start_date,remarks,record_status,pos_type_seq,created_by,created_when)"


      + " values(@cmd_seq,@tmp_emp_seq,@move_seq,@tmp_edu_seq,@old_pos_name,"
      + " @old_pos_type_seq ,@old_class_seq ,@old_job_site_name,@old_salary ,@salary,"
      + " @pos_name ,@pos_ref_no,@class_seq,@job_site_seq ,@job_site_name,"
      + " @start_date,@remarks,@record_status,@pos_type_seq,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }
        public void InsertTransfer(int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq
                , string old_pos_name, int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
                , string pos_name, int pos_type_seq, string pos_ref_no, int class_seq, int job_site_seq, string job_site_name
                , double salary
                , string ent_no, string ent_pos_name, int ent_pos_type_seq, int ent_class_seq, string ent_job_site_name
                , DateTime ent_date, string start_date, string remarks, int created_by)
        {
            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(27);

                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);

                dbManager.AddParameters(9, "@ent_no", ent_no);
                dbManager.AddParameters(10, "@ent_pos_name", ent_pos_name);
                dbManager.AddParameters(11, "@ent_pos_type_seq", ent_pos_type_seq);
                dbManager.AddParameters(12, "@ent_class_seq", ent_class_seq);
                dbManager.AddParameters(13, "@ent_job_site_name", ent_job_site_name);
                dbManager.AddParameters(14, "@ent_date", ent_date);

                dbManager.AddParameters(15, "@salary", salary);
                dbManager.AddParameters(16, "@pos_name", pos_name);
                dbManager.AddParameters(17, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(18, "@class_seq", class_seq);
                dbManager.AddParameters(19, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(20, "@job_site_name", job_site_name);
                dbManager.AddParameters(21, "@start_date", start_date);
                dbManager.AddParameters(22, "@remarks", remarks);
                dbManager.AddParameters(23, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(24, "@created_by", created_by);
                dbManager.AddParameters(25, "@created_when", DateTime.Today);
                dbManager.AddParameters(26, "@pos_type_seq", pos_type_seq);

                string sql;

                sql = " insert into hrm_ps_command_add(cmd_seq,tmp_emp_seq,move_seq,tmp_edu_seq,"
                    + " old_pos_name,old_pos_type_seq ,old_class_seq ,old_job_site_name,old_salary ,"
                    + " ent_no, ent_pos_name, ent_pos_type_seq, ent_class_seq, ent_job_site_name, ent_date,"
                    + " salary,pos_name ,pos_ref_no,class_seq,job_site_seq ,job_site_name,"
                    + " start_date,remarks,record_status,pos_type_seq,created_by,created_when)"


                    + " values(@cmd_seq,@tmp_emp_seq,@move_seq,@tmp_edu_seq,"
                    + " @old_pos_name, @old_pos_type_seq, @old_class_seq ,@old_job_site_name,@old_salary ,"
                    + " @ent_no, @ent_pos_name, @ent_pos_type_seq, @ent_class_seq, @ent_job_site_name, @ent_date,"
                    + " @salary,@pos_name ,@pos_ref_no,@class_seq,@job_site_seq ,@job_site_name,"
                    + " @start_date,@remarks,@record_status,@pos_type_seq,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        public void Update(int cmd_add_seq, int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq
            , string old_pos_name, int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
            , double salary, string pos_name, string pos_ref_no, int class_seq, int job_site_seq, string job_site_name
            , string start_date, int pos_type_seq, string remarks, int updated_by)
        {

            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {            

                dbManager.Open();
                dbManager.CreateParameters(19);
                dbManager.AddParameters(0, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);
                dbManager.AddParameters(9, "@salary", salary);
                dbManager.AddParameters(10, "@pos_name", pos_name);
                dbManager.AddParameters(11, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(12, "@class_seq", class_seq);
                dbManager.AddParameters(13, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(14, "@job_site_name", job_site_name);
                dbManager.AddParameters(15, "@start_date", Convert.ToDateTime(start_date));
                dbManager.AddParameters(16, "@remarks", remarks);
                dbManager.AddParameters(17, "@updated_by", updated_by);
                dbManager.AddParameters(18, "@updated_when", DateTime.Today);             
                

                string sql;

                sql = " update  hrm_ps_command_add set"
                + " move_seq=@move_seq,"
                + " tmp_edu_seq=@tmp_edu_seq,"
                + " old_pos_name=@old_pos_name,"
                + " old_pos_type_seq=@old_pos_type_seq,"
                + " old_class_seq=@old_class_seq,"
                + " old_job_site_name=@old_job_site_name,"
                + " old_salary=@old_salary,"
                + " salary=@salary,"
                + " pos_name=@pos_name,"
                + " pos_ref_no=@pos_ref_no,"
                + " class_seq=@class_seq,"
                + " job_site_seq=@job_site_seq,"
                + " job_site_name=@job_site_name,"
                + " start_date=@start_date,"
                + " remarks=@remarks,"
                + " pos_type_seq=@pos_type_seq,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where cmd_add_seq=@cmd_add_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        public void UpdateTransfer(int cmd_add_seq, int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq
            , string old_pos_name, int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
            , string ent_no, string ent_pos_name, int ent_pos_type_seq, int ent_class_seq, string ent_job_site_name
            , DateTime ent_date
            , double salary, string pos_name, string pos_ref_no, int class_seq, int job_site_seq, string job_site_name
            , string start_date, int pos_type_seq, string remarks, int updated_by)
        {

            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(25);
                dbManager.AddParameters(0, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);

                dbManager.AddParameters(9, "@ent_no", ent_no);
                dbManager.AddParameters(10, "@ent_pos_name", ent_pos_name);
                dbManager.AddParameters(11, "@ent_pos_type_seq", ent_pos_type_seq);
                dbManager.AddParameters(12, "@ent_class_seq", ent_class_seq);
                dbManager.AddParameters(13, "@ent_job_site_name", ent_job_site_name);
                dbManager.AddParameters(14, "@ent_date", ent_date);

                dbManager.AddParameters(15, "@salary", salary);
                dbManager.AddParameters(16, "@pos_name", pos_name);
                dbManager.AddParameters(17, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(18, "@class_seq", class_seq);
                dbManager.AddParameters(19, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(20, "@job_site_name", job_site_name);
                dbManager.AddParameters(21, "@start_date", Convert.ToDateTime(start_date));
                dbManager.AddParameters(22, "@remarks", remarks);
                dbManager.AddParameters(23, "@updated_by", updated_by);
                dbManager.AddParameters(24, "@updated_when", DateTime.Today);

                string sql;

                sql = " update hrm_ps_command_add set"
                + " move_seq=@move_seq,"
                + " tmp_edu_seq=@tmp_edu_seq,"
                + " old_pos_name=@old_pos_name,"
                + " old_pos_type_seq=@old_pos_type_seq,"
                + " old_class_seq=@old_class_seq,"
                + " old_job_site_name=@old_job_site_name,"
                + " old_salary=@old_salary,"
                + " ent_no = @ent_no,"
                + " ent_pos_name = @ent_pos_name,"
                + " ent_pos_type_seq = @ent_pos_type_seq,"
                + " ent_class_seq = @ent_class_seq,"
                + " ent_job_site_name = @ent_job_site_name,"
                + " ent_date = @ent_date,"
                + " salary=@salary,"
                + " pos_name=@pos_name,"
                + " pos_ref_no=@pos_ref_no,"
                + " class_seq=@class_seq,"
                + " job_site_seq=@job_site_seq,"
                + " job_site_name=@job_site_name,"
                + " start_date=@start_date,"
                + " remarks=@remarks,"
                + " pos_type_seq=@pos_type_seq,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where cmd_add_seq=@cmd_add_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        //public DataTable Select()
        //{
        //    try
        //    {
        //        dbManager.Open();
        //        string sql;
        //        sql = "SELECT"
        //            + " concat(prefix_name,'  ',first_name,'    ',last_name) as full_name,"
        //            + " cmd_add.job_site_name,"
        //            + " cmd_add.salary,"
        //            + " cmd_add.pos_name,"
        //            + " class.class_name,"
        //            + " cmd_add.remarks,"
        //            + " prefix.prefix_name,"
        //            + " cmd_add.cmd_add_seq,"
        //            + " cmd_add.tmp_edu_seq,"
        //            + " cmd_add.move_seq,"
        //            + " cmd_add.old_salary,"
        //            + " cmd_add.old_job_site_name,"
        //            + " cmd_add.old_pos_type_seq,"
        //            + " cmd_add.pos_ref_no,"
        //            + " cmd_add.pos_seq,"
        //            + " cmd_add.class_seq,"
        //            + " cmd_add.old_pos_name,"
        //            + " cmd_add.old_class_seq,"
        //            + " cmd_add.start_date,"
        //            + " cmd_add.pos_type_seq,"
        //            + " cmd_add.job_site_seq,"
        //            + " cmd_add.tmp_emp_seq"
        //            + " FROM"
        //            + " hrm_tmp_employees AS tmp_emp"
        //            + " Inner Join hrm_ps_command_add AS cmd_add ON cmd_add.tmp_emp_seq = tmp_emp.tmp_emp_seq"
        //            + " Inner Join hrm_mp_classes AS class ON cmd_add.class_seq = class.class_seq"
        //            + " Inner Join mas_name_prefixes AS prefix ON tmp_emp.name_prefix_seq = prefix.name_prefix_seq"
        //            + " where cmd_add.record_status = '" + SystemInfo._ActiveRecordStatus + "'";
                   

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //}


        private Boolean chkDup(int cmd_seq,int tmp_emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_ps_command_add"
                    + " where cmd_seq = " + cmd_seq
                    + " and tmp_emp_seq = " + tmp_emp_seq
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet(); 
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }               
            }

            catch (Exception ex)
            
            {
                throw(new Exception(ex.Message)) ;  
            }

            finally
            {
                dbManager.Dispose();   
            }


        }

        private Boolean chkSalary(int class_seq, double salary)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@class_seq", class_seq);
                dbManager.AddParameters(2, "@salary", salary);

                string sql;
                sql = "select * "
                    + " from hrm_pr_salary_group_dtls d"
                    + " inner join hrm_pr_salary_groups g on d.sal_grp_seq = g.sal_grp_seq"
                    + " where g.class_seq = @class_seq"
                    + " and (d.sal_min > @salary or d.sal_max < @salary)"
                    + " and d.record_status = @ActiveRecord";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }
//        public DataTable SelectTmpEmp()
//        {
//            try
//            {
//                dbManager.Open();
//                string sql;
//                sql = " SELECT "
//+ " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
//+ " emp.tmp_emp_seq "
//+ " FROM"
//+ " hrm_tmp_employees AS emp"
//+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
//+ " where emp.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";





//                DataSet ds = new DataSet();
//                DataTable dt;
//                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
//                dt = ds.Tables[0].Copy();
//                return dt;
//            }

//            catch (Exception ex)
//            {
//                return null;
//            }

//            finally
//            {
//                dbManager.Dispose();
//            }

//        }

        public DataTable SelectTmpEmpByID(int id)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
+ " concat(prefix.prefix_name,' ',emp.first_name,'   ',emp.last_name) as full_name, "
+ " emp.tmp_emp_seq, "
+ " emp.citizen,"
+ " emp.birth_date"
+ " FROM"
+ " hrm_tmp_employees AS emp"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where emp.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and tmp_emp_seq=" + id;





                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

//        public DataTable SelectStatus()
//        {
//            try
//            {
//                dbManager.Open();
//                string sql;
//                sql = " select * from mas_record_statuses  "
//                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
//                DataSet ds = new DataSet();
//                DataTable dt;
//                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
//                dt = ds.Tables[0].Copy();
//                return dt;
//            }

//            catch (Exception ex)
//            {
//                return null;
//            }

//            finally
//            {
//                dbManager.Dispose();  
//            }

//        }


 
 
        public void Delete(int cmd_add_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
               

                sql = " update hrm_ps_command_add set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where cmd_add_seq = @cmd_add_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }









        public DataTable SelectDialogOldPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogEntPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select *"
                    + " from hrm_mp_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectComboTmpEdu(int tmp_emp_seq, Nullable<int> Sequence)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                string sql;
                sql = " SELECT t.TMP_EDU_SEQ, t.EDU_QUAL_SEQ, q.EDU_QUAL_NAME, t.TMP_EMP_SEQ"
                    + ", m.EDU_MAJOR_NAME, ins.EDU_INS_NAME, t.EDU_MAJOR_SEQ"
                + " FROM HRM_TMP_EMPLOYEE_EDUCATIONS t"
                + " LEFT JOIN MAS_EDU_QUALIFICATIONS q ON t.EDU_QUAL_SEQ = q.EDU_QUAL_SEQ"
                + " LEFT JOIN MAS_EDU_MAJORS m ON t.EDU_MAJOR_SEQ = m.EDU_MAJOR_SEQ"
                + " LEFT JOIN MAS_EDU_INSTITUTES ins ON t.EDU_INS_SEQ = ins.EDU_INS_SEQ"
                + " WHERE t.RECORD_STATUS = @ActiveRecord"
                + (Sequence.HasValue ? " AND t.TMP_EDU_SEQ = @Sequence" : "")
                    + " AND t.TMP_EMP_SEQ = @tmp_emp_seq";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable BindTxtMajor(int edu_qual_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
                    + " emp_edu.edu_qual_seq,"
                    + " major.edu_major_name,"
                    + " ins.edu_ins_name"
                    + " FROM"
                    + " mas_edu_majors AS major"
                    + " Inner Join hrm_tmp_employee_educations AS emp_edu ON major.edu_major_seq = emp_edu.edu_major_seq"
                    + " Inner Join mas_edu_institutes AS ins ON emp_edu.edu_ins_seq = ins.edu_ins_seq"

                    + " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                    + " and emp_edu.edu_qual_seq = " + edu_qual_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }


        public DataTable BindPosition(int pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
                    + " pos.pos_seq,"
                    + " pos.pos_ref,"
                    + " pos.pos_name,"
                    + " pos.job_site_seq,"
                    + " field_pos.pos_type_seq"
                    + " FROM"
                    + " hrm_mp_positions AS pos"
                    + " Inner Join hrm_mp_field_positions AS field_pos ON pos.field_pos_seq = field_pos.field_pos_seq"
                    + " where pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                    + " and pos.pos_seq = " + pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }


        public DataTable BindPositionType(int field_pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " field_pos.field_pos_seq,"
+ " field_pos.field_pos_line,"
+ " type_pos.pos_type_name,"
+ " type_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_field_positions AS field_pos"
+ " Inner Join hrm_mp_position_types AS type_pos ON field_pos.pos_type_seq = type_pos.pos_type_seq"

+ " where field_pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and field_pos.field_pos_seq = " + field_pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }





    







     

   
    }
}
